# /**
# * Copyright (c) 2022 Beijing Jiaotong University
# * PhotLab is licensed under [Open Source License].
# * You can use this software according to the terms and conditions of the [Open Source License].
# *
# * You may obtain a copy of [Open Source License] at: [https://open.source.license/]
# *
# * THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
# * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
# * MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
# *
# * See the [Open Source License] for more details.
# */
# /**
# * Author: Shihao Xie
# * Created: Mar. 18, 2022
# * Supported by: National Key Research and Development Program of China
# */
import os.path
from phot_server.core.exception import ResourceNotFoundError
from phot_server.utils.singleton import singleton
from phot_server.server_config.global_config import MATERIAL_ROOT
import sqlite3

# temporarily storage fdtd data
@singleton
class MaterialRepository:

    def __init__(self):
        if self.table_exists("material") is False:
            connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
            cursor = connect.cursor()
            cursor.execute("CREATE TABLE material( \
                            id INTEGER PRIMARY KEY AUTOINCREMENT, \
                            material VARCHAR(64), \
                            color VARCHAR(16),\
                            refractive_index_table VARCHAR(64))")
            connect.commit()
            cursor.close()
        # if self.table_exists("refractive_index") is False:
        #     connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        #     cursor = connect.cursor()
        #     cursor.execute("CREATE TABLE refractive_index( \
        #                     id INTEGER PRIMARY KEY AUTOINCREMENT, \
        #                     material VARCHAR(64), \
        #                     waveLength DOUBLE, \
        #                     re DOUBLE default NULL, \
        #                     im DOUBLE default NULL\
        #                 )")
        #     connect.commit()
        #     cursor.close()
        # if self.table_exists("color") is False:
        #     connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        #     cursor = connect.cursor()
        #     cursor.execute("CREATE TABLE color( \
        #                     id INTEGER PRIMARY KEY AUTOINCREMENT, \
        #                     material VARCHAR(64), \
        #                     color VARCHAR(16)\
        #                 )")
        #     connect.commit()
        #     cursor.close()
        self.refractive_index_table = dict()
        self.update_refractive_index_table()

    def update_refractive_index_table(self):
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        cursor.execute("SELECT material,refractive_index_table FROM material")
        result = cursor.fetchall()
        self.refractive_index_table = dict(result)
        cursor.close()
        connect.close()


    def add_material(self, material_name, color, refractive_index_points):
        if self.refractive_index_table.get(material_name, None) is not None:
            raise Exception("Material '{}' is exist".format(material_name))
        refractive_index_table_name = material_name.replace("(", "_").replace(")", "")
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        cursor.execute("INSERT INTO material(material, color, refractive_index_table) VALUES('{}', '{}', '{}')".format(material_name, color, refractive_index_table_name))
        connect.commit()
        cursor.close()
        self.add_material_property(material_name, refractive_index_points)
        self.update_refractive_index_table()

    def add_material_property(self, material_name: str, refractive_index_points):
        refractive_index_table_name = self.refractive_index_table[material_name] \
            if self.refractive_index_table.get(material_name, None) is not None \
            else material_name.replace("(", "_").replace(")", "")
        if self.table_exists(refractive_index_table_name) is False:
            connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
            cursor = connect.cursor()
            sql = "CREATE TABLE {}( id INTEGER PRIMARY KEY AUTOINCREMENT, \
                            waveLength DOUBLE, \
                            re DOUBLE default NULL, \
                            im DOUBLE default NULL\
                        )".format(refractive_index_table_name)
            cursor.execute(sql)
            connect.commit()
            cursor.close()
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        for wave_length, re, im in refractive_index_points:
            sql = "INSERT INTO {}(waveLength, re, im) VALUES ({}, {}, {})" \
                .format(refractive_index_table_name,
                        wave_length,
                        re if re is not None and re != "nan" else 0,
                        im if im is not None and im != "nan" else 0)
            cursor.execute(sql)
        connect.commit()
        cursor.close()

    def material_exists(self, material_name):
        self.update_refractive_index_table()
        return True if self.refractive_index_table.get(material_name, None) is not None else False

    def get_material_color(self, material_name):
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        cursor.execute("SELECT color FROM material WHERE material='{}'".format(material_name))
        result = cursor.fetchone()
        cursor.close()
        return result[0]

    def get_material_refractive_index(self, material_name, top=-1):
        refractive_index_table_name = self.refractive_index_table[material_name]
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        cursor.execute(
            "SELECT id,waveLength,re,im FROM {}".format(refractive_index_table_name))
        if top != -1:
            results = cursor.fetchmany(top)
        else:
            results = cursor.fetchall()
        # for rid in range(0, len(results)):
        #     results[rid][0] = rid
        cursor.close()
        return results

    def get_material_refractive_index_by_wave_length(self, material_name, wave_length=1.0):
        refractive_index_table_name = self.refractive_index_table[material_name]
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        cursor.execute(
            "SELECT re,im FROM {} WHERE waveLength={}".format(refractive_index_table_name, wave_length))
        results = cursor.fetchone()
        cursor.close()
        return results

    def update_material(self, material_name, color, refractive_index_points):
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        cursor.execute("UPDATE material SET color='{}' WHERE material='{}'".format(color, material_name))
        refractive_index_table_name = self.refractive_index_table[material_name]
        for rid, wave_length, re, im in refractive_index_points:
            # cursor.execute("UPDATE {} SET waveLength={}, re={}, im={} WHERE id={}"
            #                .format(refractive_index_table_name, wave_length, re, im, rid))
            cursor.execute("INSERT INTO {table_name}(id, waveLength, re, im) VALUES({rid},{wl},{re},{im}) \
                           ON CONFLICT (id) DO UPDATE SET waveLength={wl}, re={re}, im={im}"
                           .format(table_name=refractive_index_table_name,
                                   rid=rid,
                                   wl=wave_length,
                                   re=re,
                                   im=im))
        connect.commit()
        cursor.close()

    def update_material_property(self, material_name, refractive_index):
        refractive_index_table_name = self.refractive_index_table[material_name]
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        re = refractive_index.real
        im = refractive_index.imag
        cursor.execute("INSERT INTO {table_name}(id, waveLength, re, im) VALUES({rid},{wl},{re},{im}) \
                                   ON CONFLICT (id) DO UPDATE SET waveLength={wl}, re={re}, im={im}"
                       .format(table_name=refractive_index_table_name,
                               rid=refractive_index.propertyId,
                               wl=refractive_index.waveLength,
                               re=re if re is not None and re != "nan" else 0,
                               im=im if im is not None and im != "nan" else 0))
        # cursor.execute(
        #     "SELECT id,waveLength,re,im FROM {} WHERE id='{}'".format(refractive_index_table_name, refractive_index.propertyId))
        # result = cursor.fetchone()
        # if result is not None:
        #     cursor.execute("UPDATE {} SET waveLength={}, re={}, im={} WHERE id={}"
        #                    .format(refractive_index_table_name, refractive_index.waveLength, refractive_index.real,
        #                            refractive_index.imag, refractive_index.propertyId))
        # else:
        #     wave_length = refractive_index.waveLength
        #     re = refractive_index.real
        #     im = refractive_index.imag
        #     cursor.execute("INSERT INTO {}(waveLength, re, im) VALUES ({}, {}, {})" \
        #                    .format(refractive_index_table_name,
        #                            wave_length,
        #                            re if re is not None and re != "nan" else 0,
        #                            im if im is not None and im != "nan" else 0))
        connect.commit()
        cursor.close()

    def table_exists(self, table_name):
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", (table_name,))
        result = cursor.fetchone()
        cursor.close()
        if result:
            return True
        else:
            return False

    def get_material_name_list(self):
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        cursor.execute("SELECT DISTINCT material FROM material")
        results = cursor.fetchall()
        cursor.close()
        return [name[0] for name in results]

    def delete_material(self, material_name):
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        cursor.execute("DELETE FROM material WHERE material='{}'".format(material_name))
        refractive_index_table_name = self.refractive_index_table[material_name]
        # cursor.execute("DELETE FROM {}".format(refractive_index_table_name))
        cursor.execute("DROP TABLE {}".format(refractive_index_table_name))
        connect.commit()
        cursor.close()
        self.update_refractive_index_table()

    def delete_material_property(self, material_name, property_id):
        connect = sqlite3.connect(os.path.join(MATERIAL_ROOT, "material.db"))
        cursor = connect.cursor()
        refractive_index_table_name = self.refractive_index_table[material_name]
        cursor.execute("DELETE FROM {} WHERE id={}".format(refractive_index_table_name, property_id))
        connect.commit()
        cursor.close()
